package edu.gatech.oad;

import android.database.Cursor;
import android.database.sqlite.*;
import android.content.Context;
import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Class to create database and supply methods to use it.
 * @author Steven Wojcio
 *
 */
public class DataBaseHelper extends SQLiteOpenHelper{
	 
	
	//Non-static variable reflecting the user that is currently logged in
	
    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/edu.gatech.oad/databases/";
 
    private static String DB_NAME = "dbV1";
 
    private SQLiteDatabase myDataBase; 
 
    private final Context myContext;
 
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {
 
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }	
 
  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		return;
    	}else{
 
    		//By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
        	this.getReadableDatabase();
        	try {
    			copyDataBase();
    		} catch (IOException e) {
        		System.out.println(e.getMessage());
        	}

    	}
 
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    	}catch(SQLiteException e){
    		System.out.println(e.getMessage());
    		//database does't exist yet.
    	}
 
    	if(checkDB != null){
 
    		checkDB.close();
 
    	}
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open("WMS_DataBase");
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    /**
     * Opens database if it exists. Throws exception if not.
     * @throws SQLException
     */
    public void openDataBase() throws SQLException{
 
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

		//BELOW IS THE CODE TO RESET DATABASES. JUST UNCOMMENT AND RUN ONCE (LOG IN AND THEN X OUT OF APP).
/*		for(String s:myDataBase.rawQuery("Select * from Item_Transaction", null).getColumnNames()) System.out.println("User transaction: "+ s);
		try{
			System.out.println("Trying to drop tables");
			myDataBase.execSQL("DROP TABLE Item_Transaction");
			System.out.println("IT Dropped");
			myDataBase.execSQL("DROP TABLE Item");
			System.out.println("Item Dropped");
			myDataBase.execSQL("Create Table Item (_id Integer Primary Key, Status Varchar(20), Item_ID varchar(20), Category varchar(20), Location varchar(50), Description varchar(150), Date date, User VARCHAR(50))");
			
			}
			catch(Exception e){}
    	myDataBase.execSQL("Delete From User where Username = 'admin1'");
    		myDataBase.execSQL("Delete From User where Username = 'admin2'");
    		registerUser("admin1","adminforthewin","S","Wojcio","Active","Admin");
    		registerUser("admin2","adminforthewin","Barack","Obama","Active","Admin");
    		registerUser("boss","aaaa","Steve","Waj","Active","Admin"); 
 */   	

    	
    	if (!userExists("user1")){
    		
    		
    		registerUser("user1","password","Bruce","Stout","Active","Regular");
    		registerUser("user2","password","Michael","Scott","Active","Regular");
    		registerUser("user3","password","Ron","Swanson","Active","Regular");
    		registerUser("user4","password","Hunter","Brennick","Active","Regular");
    		registerUser("user5","password","Gabby","Bales","Active","Regular");
    		registerUser("admin1","adminforthewin","S","Wojcio","Active","Admin");
    		registerUser("admin2","adminforthewin","Barack","Obama","Active","Admin");
    		
    		addItem("Lost","Books","Harry Potter 4", "Atlanta,GA","2013-03-25", "user1");
    		addItem("Lost","Books","Harry Potter 4", "Atlanta,GA","2013-03-26", "user1");
    		addItem("Lost","Books","Harry Potter 5", "Atlanta,GA","2013-03-26", "user1");
    		addItem("Lost","Books","Harry Potter 6", "Atlanta,GA","2013-03-24", "user1");
    		addItem("Lost","Books","Harry Potter 3", "Atlanta,GA","2013-03-23", "user1");
    		addItem("Found","Books","Harry Potter 3", "Atlanta,GA","2013-03-27", "user2");
    		addItem("Found","Books","Harry Potter 4", "Atlanta,GA","2013-03-27", "user2");
    	}
    }
 
    /**
     * closes database, but makes sure that it is not null first (override)
     */
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
 
	}
 
    /**
     * Must do this. Just overwrites the onCreate method. This used to have create table statements but they are not needed anymore.
     */
	@Override
	public void onCreate(SQLiteDatabase db) {
 
	}
 
	/**
	 * same as onCreate
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
	
	/**
	 * Pre: Valid strings for Username and Password. Valid strings should not contain any SQL as they will be interpreted as code. 
	 * Post: The stirngs will be compared to the database and a value will be returned indicating whether or not the credentials are valid.
	 * @param un Username
	 * @param pw Password
	 * @return True if valid, False if not
	 */
	public boolean checkUNAndPW(String un, String pw){
		
		try{
			Cursor c = myDataBase.rawQuery("Select Password,AccountStatus from user where Username = ?",new String[] {un});
			if (c.moveToFirst()){
				String storedPW = c.getString(0);
				if (storedPW.equals(pw)){
					if (!c.getString(1).equals("Locked")){
						return true;
					}
				}
			}
			return false;
		}
		catch(Exception E){
			return false;
		}
	}
	/**
	 * Pre: Six Strings, all non-null and not containing SQL injections.
	 * Post: New User exists in database.
	 * @param Username 
	 * @param Password
	 * @param FirstName
	 * @param LastName
	 * @param AccountStatus Active, Locked, or Deleted
	 * @param Type Admin or Regular
	 * @return True if added, False if error
	 */
	public boolean registerUser(String Username, String Password, String FirstName, String LastName, String AccountStatus, String Type){
		if(userExists(Username))return false;
		try{
			myDataBase.execSQL("Insert into User (Username,Password,FirstName,LastName,AccountStatus,Type) Values " +
					"('"+Username+"','"+Password+"','"+FirstName+"','"+LastName+"','"+AccountStatus+"','"+Type+"')");
			return true;
		}
		catch(Exception E){
			return false;
		}
	}
	
	/**
	 * Pre: Valid strings (non null)
	 * Post: Item added to database
	 * @param Category
	 * @param Description
	 * @param User
	 * @param Location
	 * @return true if item successfully added
	 */
	public boolean addItem(String Status, String Category, String Description, String Location, String Date, String User){
		try{
			
			myDataBase.execSQL("Insert into Item (Status, Item_ID, Category, Location, Description, Date, User) Values " +
					"('"+Status+"','"+"uselessForNow"+"','"+Category+"','"+Location+"','"+ Description+ "','" +Date + "','"+User+"')");
			
			return true;
		}
		catch(Exception E){
			return false;
		}
	}


	
	/**
	 * Deletes user from the database
	 * 
	 * @param user the current user
	 * */
	public boolean deleteUser(String un){
		if (!userExists(un))return false;
		try{
			myDataBase.execSQL("Delete from User Where Username = '"+un+"'");
			return true;
		}
		catch(Exception E){
			return false;
		}
	}
	
	/**
	 * Checks if a given username corresponds with a user in the table
	 * @param un The username to check
	 * @return boolean (t if exists, f if not)
	 */
	public boolean userExists(String un){
		try{
			Cursor c = myDataBase.rawQuery("Select Username from user where Username = ?",new String[] {un});
			if (c.getCount()>0)return true;
			return false;
		}
		catch(Exception E){
			return false;
		}
	}

	/**
	 * pre: true
	 * post: list of items in the database
	 * @return arraylist containing all of the items in the db
	 */
	public ArrayList<Item> getItems(){
		Cursor c = myDataBase.rawQuery("Select * from Item", null);
		ArrayList<Item> res = new ArrayList<Item>();
		if (!c.moveToFirst())return res;
		
		res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));

		while (c.moveToNext()){
			System.out.println(c.getString(1));
			//System.out.println("Adding new item where " + "status is "+c.getString(1)+" and cat  is "+ c.getString(3)+ "and loc is "+ c.getString(4)+ " and desc is "+c.getString(5)+ "and date is "+ c.getString(6)+ " and user is "+ c.getString(7));
			res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));
		}
		return res;
	}
	
	/**
	 * returns the best matching item for a given item
	 * @param i The item to look for a match against
	 * @return The best match, or null if the db is empty
	 */
	public Item bestMatch(Item i){
		ArrayList<Item> potentials = getItems();
		if(potentials.size()==0){
			return null;
		}
		int max = potentials.get(0).compare(i);
		Item res = potentials.get(0);
		for (Item item:potentials){
			System.out.println(item.itemDescription + " gets strength " + item.compare(i));
			if (item.compare(i)>=max){
				max = item.compare(i);
				res = item;
			}
		}
		return res;
	}
	
	/**
	 * pre: true
	 * post: list of items in the database
	 * @param search term
	 * @return items matching search
	 */
	public ArrayList<Item> searchItems(String search){
		//Status Varchar(20), Item_ID varchar(20), Category varchar(20), Location varchar(50), Description varchar(150), Date date, User VARCHAR(50))
		search = "%" + search + "%";
		Cursor c = myDataBase.rawQuery("Select * from Item where Status LIKE '"+search+"' OR Category LIKE '"+search+"' OR Location LIKE '"+search+"' OR Description LIKE '"+search+"' OR Date LIKE '"+search+"' OR User LIKE '"+search+"'", null);
		ArrayList<Item> res = new ArrayList<Item>();
		if (!c.moveToFirst())return res;
		
		res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));

		while (c.moveToNext()){
			System.out.println(c.getString(1));
			//System.out.println("Adding new item where " + "status is "+c.getString(1)+" and cat  is "+ c.getString(3)+ "and loc is "+ c.getString(4)+ " and desc is "+c.getString(5)+ "and date is "+ c.getString(6)+ " and user is "+ c.getString(7));
			res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));
		}
		return res;
	}
	
	/**
	 * This method filters the db
	 * @param Status
	 * @param StartDate
	 * @param EndDate
	 * @param Category
	 * @return filtered list of items, or empty arraylist if none fit
	 */
	public ArrayList<Item> getItemsBy(String Status,String StartDate, String EndDate, String Category){
		Cursor c;
		if(Status == "none" && Category == "none"&& StartDate=="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Category = '" + Category + "' AND (Date>= '"+ StartDate + "' AND Date<= '"+EndDate+"')", null);
		else if(Status == "none" && Category!="none" && StartDate!="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Category = '" + Category + "' AND (Date>= '"+ StartDate + "' AND Date<= '"+EndDate+"')", null);
		else if(Status != "none" && Category == "none"&& StartDate!="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND (Date>= '"+ StartDate + "' AND Date<= '"+EndDate+"')", null);
		else if(Status != "none" && Category != "none"&& StartDate=="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Category = '" + Category + "' AND Date<= '"+EndDate+"'", null);
		else if(Status != "none" && Category != "none"&& StartDate!="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Category = '" + Category + "' AND Date>= '"+ StartDate + "' ", null);
		
		else if(Status == "none" && Category == "none"&& StartDate!="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where (Date>= '"+ StartDate + "' AND Date<= '"+EndDate+"')", null);
		else if(Status != "none" && Category == "none"&& StartDate=="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Date<= '"+EndDate+"'", null);
		else if(Status != "none" && Category != "none"&& StartDate=="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Category = '" + Category + "'", null);
		else if(Status != "none" && Category == "none"&& StartDate!="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Date>= '"+ StartDate + "'", null);
		else if(Status == "none" && Category != "none"&& StartDate=="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Category = '" + Category + "' AND Date>= '"+ StartDate + "'", null);
		else if(Status == "none" && Category != "none"&& StartDate!="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Category = '" + Category + "' AND Date>= '"+ StartDate + "'", null);
		
		else if(Status != "none" && Category == "none"&& StartDate=="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"'", null);
		else if(Status == "none" && Category != "none"&& StartDate=="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Category= "+"'"+Category+"'", null);
		else if(Status == "none" && Category == "none"&& StartDate!="none" && EndDate=="none")
		c = myDataBase.rawQuery("Select * from Item where Date>= "+"'"+StartDate+"'", null);
		else if(Status == "none" && Category == "none"&& StartDate=="none" && EndDate!="none")
		c = myDataBase.rawQuery("Select * from Item where Date<= "+"'"+EndDate+"'", null);
		else c = myDataBase.rawQuery("Select * from Item where Status= "+"'"+Status+"' AND Category = '" + Category + "' AND (Date>= '"+ StartDate + "' AND Date<= '"+EndDate+"')", null);
		
		ArrayList<Item> res = new ArrayList<Item>();
		
		if (!c.moveToFirst())return res;
		
		res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));
		while (c.moveToNext()){
			res.add(new Item(c.getString(1),c.getString(3),c.getString(4),c.getString(5),c.getString(6),c.getString(7)));
		}
		return res;
	}
	
	/**
	 * Locks a currently active user class
	 * 
	 * @param user the current user
	 * @return boolean reppin if the user is locked or not
	 * */ 
	public boolean lockUser(String user){
		if (!userExists(user))return false;
		System.out.println("Locking user " + user);
		try{
			myDataBase.execSQL("Update User Set AccountStatus='Locked' where Username = '" + user + "'");
			System.out.println(user + " Account Locked");
			return true;
		}
		catch(Exception E){
			return false;
		}
	}
	
	/**
	 * Unlocks a currently locked user class
	 * 
	 * @param user the current user
	 * */
	public void unlockUser(String user){
		if (!userExists(user))return;
		System.out.println("Unlocking user " + user);
		try{
			myDataBase.execSQL("Update User Set AccountStatus='Active' where Username = '" + user + "'");
			System.out.println(user + " Account unlocked");
			return;
		}
		catch(Exception E){
			System.out.println(E.getMessage());
		}
	}
	
	/**
	 * check if current user is locked 
	 * 
	 * @param user the current user
	 * @return boolean true is currUser account is locked
	 * */
	public boolean userLocked(String user){
		if (!userExists(user))return false;
		System.out.println("Checking user " + user);
		try{
			Cursor c = myDataBase.rawQuery("Select AccountStatus from user where Username = ?",new String[] {user});
			if (c.moveToFirst()){
				System.out.println(c.getString(0));
				if(c.getString(0).equals("Locked")){
					System.out.println("locked");
					return true;
				}
				return false;
			}
		}
		catch(Exception E){
			return false;
		}
		return false;
	}
	
	/**
	 * check if current user is an admin 
	 * 
	 * @param user the current user
	 * @return boolean true is currUser has admin status
	 * */
	public boolean checkAdminStatus(String user){
		if (!userExists(user))return false;
		System.out.println("Checking user " + user);
		try{
			Cursor c = myDataBase.rawQuery("Select Type from user where Username = '"+user+"'",null);
			if (c.moveToFirst()){
				System.out.println(c.getString(0));
				if(c.getString(0).equals("Admin")){
					System.out.println("Is Admin");
					return true;
				}
				System.out.println("Not Admin");
				return false;
			}
		}
		catch(Exception E){
			return false;
		}
		return false;
	}
	
	
	/**
	 * This just returns all of the users.
	 * @param cat either locked or active, reppin the type of account
	 * @return specified users
	 */
	public ArrayList<User> getUsers(String cat){
		Cursor c;
		if (cat == "all"){
			c = myDataBase.rawQuery("Select * from User",null);
		}
		else{
			c = myDataBase.rawQuery("Select * from User where AccountStatus = '" + cat +"'",null);
		}
		ArrayList<User> res = new ArrayList<User>();
		c.moveToFirst();
		res.add(new User(c.getString(1),c.getString(2),c.getString(3),c.getString(4),c.getString(5),c.getString(6)));
		while (c.moveToNext()){
			res.add(new User(c.getString(1),c.getString(2),c.getString(3),c.getString(4),c.getString(5),c.getString(6)));
		}
		return res;
	}
 
}